# coding: utf-8
# 用来创建数据库模型
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import sys
from collections import defaultdict
from openpyxl import load_workbook
from sqlalchemy import and_

app = Flask(__name__)
app.config.from_pyfile('/data/mngsSYS/b/app/config.py')
loci = app.config[sys.argv[2]]
db = SQLAlchemy(app)

class JobSample(db.Model):
    __tabname__ = 'jobsample'
    id = db.Column(db.Integer, primary_key=True)
    laboratory = db.Column(db.String(8), index=True)
    date = db.Column(db.Date, index=True)
    jobid = db.Column(db.String(64), index=True)
    samplenames = db.Column(db.String(128), index=True)
    addtime = db.Column(db.DateTime, default=datetime.now)

if __name__ == '__main__':
    with open(sys.argv[1],encoding='gbk') as f:
        samples = defaultdict(list)
        flag = 0
        for i in f:
            if flag == 0:
                if i.startswith('Sample_ID'):
                    flag = 1
                    continue
                continue
            if flag == 1:
                #edit 211218
                #if '-YF' not in i and 'NC' not in i and '-KY' not in i and 'RY' not in i and '-SY' not in i:
                if '-YF' not in i and 'NC' not in i  and 'RY' not in i and 'BY' not in i:
                    k = i.split(',')[0].split('-')[0]
                    samples[k].append(i.split(',')[0])
    dt = datetime.now().date()
    try:
        for k, v in sorted(samples.items(), key=lambda item:item[0]):
            sns = ','.join(v)
            p = JobSample.query.filter(and_(JobSample.jobid == sys.argv[3], JobSample.samplenames == sns)).first()
            if p:
                continue
            else:
                js = JobSample(laboratory=loci, date=dt, jobid=sys.argv[3], samplenames=sns)
                db.session.add(js)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        exit('任务样本导入出错！数据库回滚完毕！')
